import random
import string

letter = string.ascii_uppercase
digit = string.digits
token = letter + digit
loc_suffix = ['楼', '园', '学院', '站', '馆', '场', '厂', '屯', '公寓', '堂']


def GBK2312():
    head = random.randint(0xb0, 0xe7)
    body = random.randint(0xa1, 0xfe)
    val = f'{head:x} {body:x}'
    word = bytes.fromhex(val).decode('gb2312', errors='ignore')
    return word


def data_dept(cur, num=10):
    """
    向department插入数据,由于是新建的系，此时系中没有教师，也就不会有负责人

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    data = []
    for i in range(num):
        line = [
            ''.join(random.choices(population=letter, k=2)) + ''.join(random.choices(population=digit, k=3)),  # 系号
            ''.join([GBK2312() for i in range(random.randint(3, 5))]),  # 系名
            ''.join([GBK2312() for i in range(random.randint(2, 3))]) + ''.join(random.choice(loc_suffix)),  # 地点
            None  # 无管理者
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO department VALUES (%s,%s,%s,%s)", data[i])


def data_teacher(cur, num=10):
    """
    向teacher插入数据,教师必须归属于某个系

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    cur.execute("SELECT did FROM department")
    rows = cur.fetchall()
    did = []
    for row in rows:
        did.append(row[0])
    data = []
    for i in range(num):
        line = [
            ''.join(random.choices(population=token, k=10)),  # 编号
            ''.join([GBK2312() for i in range(random.randint(2, 4))]),  # 姓名
            random.randint(5000, 20000),  # salary
            ''.join([GBK2312() for i in range(random.randint(2, 3))]) + ''.join(random.choice(loc_suffix)),  # 地址
            random.sample(did, 1)[0]  # 系号
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO teacher VALUES (%s,%s,%s,%s,%s)", data[i])


def data_classroom(cur, num=10):
    """
    向classroom插入数据

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    data = []
    for i in range(num):
        line = [
            ''.join([GBK2312() for i in range(random.randint(2, 3))]) + ''.join(random.choice(loc_suffix)),  # 地点
            ''.join(random.choices(population=digit, k=random.randint(2, 3))),  # 姓名
            random.randint(10, 200),  # 容量
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO classroom VALUES (%s,%s,%s)", data[i])


def data_student(cur, num=10):
    """
    向student插入数据

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    cur.execute("SELECT did FROM department")
    rows = cur.fetchall()
    did = []
    for row in rows:
        did.append(row[0])
    data = []
    for i in range(num):
        line = [
            ''.join(random.choices(population=digit, k=10)),  # 编号
            ''.join([GBK2312() for i in range(random.randint(2, 4))]),  # 姓名
            ''.join(random.choices(population=digit, k=11)),  # 电话号码
            random.sample(did, 1)[0]  # 系号
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO student VALUES (%s,%s,%s,%s)", data[i])


def data_course(cur, num=10):
    """
    向student插入数据

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    cur.execute("SELECT did FROM department")
    rows = cur.fetchall()
    did = []
    for row in rows:
        did.append(row[0])
    cur.execute("SELECT thid FROM teacher")
    rows = cur.fetchall()
    thid = []
    for row in rows:
        thid.append(row[0])
    cur.execute("SELECT loc,room FROM classroom")
    rows = cur.fetchall()
    loc_room = []
    for row in rows:
        loc_room.append(row[0:2])
    data = []
    for i in range(num):
        temp = random.sample(loc_room, 1)[0]
        line = [
            random.sample(did, 1)[0] + ''.join(random.choices(population=digit, k=5)),  # 课号
            ''.join([GBK2312() for i in range(random.randint(6, 10))]),  # 课名
            random.randint(1, 5),  # 学分
            temp[0],  # loc
            temp[1],  # room
            None,
            None,
            random.sample(did, 1)[0],  # 系号
            random.sample(thid, 1)[0]  # 教师
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO course VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)", data[i])


def data_project(cur, num=10):
    """
    向project插入数据

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    cur.execute("SELECT thid FROM teacher")
    rows = cur.fetchall()
    thid = []
    for row in rows:
        thid.append(row[0])
    data = []
    for i in range(num):
        line = [
            'PRO' + ''.join(random.choices(population=digit, k=7)),  # 编号
            ''.join([GBK2312() for i in range(random.randint(4, 8))]),  # 项目名
            random.sample(thid, 1)[0]  # 指导教师
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO project VALUES (%s,%s,%s)", data[i])


def data_contacts(cur, num=10):
    """
    向contacts插入数据

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    cur.execute("SELECT sid FROM student")
    rows = cur.fetchall()
    sid = []
    for row in rows:
        sid.append(row[0])
    data = []
    for i in range(num):
        line = [
            ''.join([GBK2312() for i in range(random.randint(2, 4))]),  # 姓名
            random.sample(sid, 1)[0],  # 关联的学号
            ''.join(random.choices(population=digit, k=11)),  # 电话号码
            random.sample(['F', 'M'], 1)[0]  # 性别
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO contacts VALUES (%s,%s,%s,%s)", data[i])


def data_take(cur, num=10):
    """
    向take插入数据

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    cur.execute("SELECT sid FROM student")
    rows = cur.fetchall()
    sid = []
    for row in rows:
        sid.append(row[0])
    cur.execute("SELECT coid FROM course")
    rows = cur.fetchall()
    coid = []
    for row in rows:
        coid.append(row[0])
    data = []
    for i in range(num):
        line = [
            random.sample(sid, 1)[0],  # 学号
            random.sample(coid, 1)[0]  # 课号
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO take VALUES (%s,%s)", data[i])


def data_works(cur, num=10):
    """
    向take插入数据

    :param num: 生成的数据个数
    :param cur: 数据库操作所使用光标
    """
    cur.execute("SELECT sid FROM student")
    rows = cur.fetchall()
    sid = []
    for row in rows:
        sid.append(row[0])
    cur.execute("SELECT pid FROM project")
    rows = cur.fetchall()
    pid = []
    for row in rows:
        pid.append(row[0])
    data = []
    for i in range(num):
        line = [
            random.sample(sid, 1)[0],  # 学号
            random.sample(pid, 1)[0]  # 课号
        ]
        data.append(line)
    for i in range(num):
        cur.execute("INSERT INTO works VALUES (%s,%s)", data[i])
